﻿CREATE TABLE messages (
	id serial not null primary key,
	id_task_student integer,
	id_sender integer,
	id_reciever integer,
	id_group integer,
	description character varying,
	updated timestamp
);

CREATE TABLE tasks (
	id serial not null primary key,
	id_mentor integer,
	details integer,
	student_pts numeric,
	level numeric,
	updated timestamp,
	avail_to timestamp
);

CREATE TABLE task_students (
	id serial not null primary key,
	id_task integer,
	id_student integer,
	mentor_pts numeric,
	updated timestamp
);

CREATE TRIGGER change_task_students_t
BEFORE INSERT OR UPDATE ON task_students FOR EACH ROW EXECUTE PROCEDURE change_task_students();

CREATE OR REPLACE FUNCTION change_task_students() RETURNS trigger AS $$
    BEGIN
        NEW.update := current_timestamp;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER change_tasks_t
BEFORE INSERT OR UPDATE ON tasks FOR EACH ROW EXECUTE PROCEDURE change_tasks();

CREATE OR REPLACE FUNCTION change_tasks() RETURNS trigger AS $$
    BEGIN
        NEW.update := current_timestamp;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER change_messages_t
BEFORE INSERT OR UPDATE ON messages FOR EACH ROW EXECUTE PROCEDURE change_messages();

CREATE OR REPLACE FUNCTION change_messages() RETURNS trigger AS $$
    BEGIN
        NEW.update := current_timestamp;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER change_users_t
BEFORE UPDATE  ON users FOR EACH ROW EXECUTE PROCEDURE change_users();

CREATE TRIGGER insert_users
BEFORE INSERT ON users FOR EACH ROW EXECUTE PROCEDURE insert_users();

CREATE OR REPLACE FUNCTION insert_users() RETURNS trigger AS $$
    BEGIN
        NEW.created := current_timestamp;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION change_users() RETURNS trigger AS $$
    BEGIN
        NEW.update := current_timestamp;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;